import logging
from typing import List

from fastapi import APIRouter, Depends, Query
from pydantic import BaseModel, Field
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

data_mrk_head = APIRouter(prefix="/container/mrkHead", tags=["容器报废管理"])


class ContainerScrapQueryParams(PaginationBase):
    # 筛选条件
    fsiteNo: str = Field("", description="站点编号")
    ftakedateStart: str = Field("", description="开始日期(yyyyMMdd)")
    ftakedateEnd: str = Field("", description="结束日期(yyyyMMdd)")
    fbilltype: str = Field("", description="单据类型")
    fmrkuser: str = Field("", description="标记人")
    fstockno: str = Field("", description="库存编号")
    fbillno: str = Field("", description="单据编号")
    fbillstate: str = Field("", description="单据状态")


@data_mrk_head.get("/list", summary="容器报废查询接口")
def list_mrk_head(query: ContainerScrapQueryParams = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from i_pack_mrk_head where 1=1"
    if query.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = query.fsiteNo
    if query.fbillno != "":
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = query.fbillno
    if query.fmrkuser != "":
        sql = sql + " and fmrkuser = :fmrkuser "
        param["fmrkuser"] = query.fmrkuser
    if query.fstockno != "":
        sql = sql + " and fstockno = :fstockno "
        param["fstockno"] = query.fstockno
    if query.fbilltype != "":
        sql = sql + " and fbilltype = :fbilltype "
        param["fbilltype"] = query.fbilltype
    if query.fbillstate != "":
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = query.fbillstate
    if (query.ftakedateStart != "") and (query.ftakedateEnd != ""):
        sql = sql + " and to_char(fmrkdate,'yyyymmdd') between  :ftakedateStart  and :ftakedateEnd "
        param["ftakedateStart"] = query.ftakedateStart
        param["ftakedateEnd"] = query.ftakedateEnd
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fbillno,fsite_no,fmrkuser,fmrkdate,fstockno,"
    sql = sql + " fitemcount,fpiececount,ftotalamt,fexplanation,fupddate,"
    sql = sql + " foperator,fcheckdate,fcheckuser,fbillstate,fbilltype"
    sql = sql + " from i_pack_mrk_head where 1=1"
    if query.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = query.fsiteNo
    if query.fbillno != "":
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = query.fbillno
    if query.fmrkuser != "":
        sql = sql + " and fmrkuser = :fmrkuser "
        param["fmrkuser"] = query.fmrkuser
    if query.fstockno != "":
        sql = sql + " and fstockno = :fstockno "
        param["fstockno"] = query.fstockno
    if query.fbilltype != "":
        sql = sql + " and fbilltype = :fbilltype "
        param["fbilltype"] = query.fbilltype
    if query.fbillstate != "":
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = query.fbillstate
    if (query.ftakedateStart != "") and (query.ftakedateEnd != ""):
        sql = sql + " and to_char(fmrkdate,'yyyymmdd') between  :ftakedateStart  and :ftakedateEnd "
        param["ftakedateStart"] = query.ftakedateStart
        param["ftakedateEnd"] = query.ftakedateEnd
    if query.order == "desc":
        sql = sql + " order by  fbillno desc "
    else:
        sql = sql + " order by fbillno "
    sql = sql + " limit :limit offset :offset "
    param["offset"] = query.offset
    param["limit"] = query.limit
    data = db.execute(text(sql), param).fetchall()

    # details 明细列表
    param = {}
    sql_3 = " select b.fbillno,b.fline,b.frfidbarcode,b.fpackno,c.fpackname"
    sql_3 = sql_3 + " from i_pack_mrk_head a,i_pack_mrk_detail b,i_ts_pack c  "
    sql_3 = sql_3 + " where a.fbillno=b.fbillno"
    sql_3 = sql_3 + " and b.fpackno=c.fpackno"
    if query.fsiteNo != "":
        sql_3 = sql_3 + " and a.fsite_no = :site_no "
        param["site_no"] = query.fsiteNo
    if query.fbillno != "":
        sql_3 = sql_3 + " and a.fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = query.fbillno
    if query.fmrkuser != "":
        sql_3 = sql_3 + " and a.fmrkuser = :fmrkuser "
        param["fmrkuser"] = query.fmrkuser
    if query.fstockno != "":
        sql_3 = sql_3 + " and a.fstockno = :fstockno "
        param["fstockno"] = query.fstockno
    if query.fbilltype != "":
        sql_3 = sql_3 + " and a.fbilltype = :fbilltype "
        param["fbilltype"] = query.fbilltype
    if query.fbillstate != "":
        sql_3 = sql_3 + " and a.fbillstate = :fbillstate "
        param["fbillstate"] = query.fbillstate
    if (query.ftakedateStart != "") and (query.ftakedateEnd != ""):
        sql_3 = sql_3 + " and to_char(a.fmrkdate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
        param["ftakedateStart"] = query.ftakedateStart
        param["ftakedateEnd"] = query.ftakedateEnd
    sql_3 = sql_3 + " order by b.fbillno,b.fline"
    data_detail = db.execute(text(sql_3), param).fetchall()

    data_list = []
    for key in range(len(data)):
        fmrkdate = ""
        try:
            fmrkdate = data[key][3].strftime('%Y-%m-%dT%H:%M:%S')
        except Exception as e:
            print(e)

        if (fmrkdate == ""):
            fmrkdate = None

        fupddate = ""
        try:
            fupddate = data[key][9].strftime('%Y-%m-%dT%H:%M:%S')
        except Exception as e:
            print(e)

        if (fupddate == ""):
            fupddate = None

        fcheckdate = ""
        try:
            fcheckdate = data[key][11].strftime('%Y-%m-%dT%H:%M:%S')
        except Exception as e:
            print(e)

        if (fcheckdate == ""):
            fcheckdate = None

        d1 = {}
        d1['fbillno'] = data[key][0]
        d1['fsiteNo'] = data[key][1]
        d1['fmrkuser'] = data[key][2]
        d1['fmrkdate'] = fmrkdate
        d1['fstockno'] = data[key][4]
        d1['fitemcount'] = data[key][5]
        d1['fpiececount'] = data[key][6]
        d1['ftotalamt'] = data[key][7]
        d1['fexplanation'] = data[key][8]
        d1['fupddate'] = fupddate
        d1['foperator'] = data[key][10]
        d1['fcheckdate'] = fcheckdate
        d1['fcheckuser'] = data[key][12]
        d1['fbillstate'] = data[key][13]
        d1['fbilltype'] = data[key][14]
        sbillno = data[key][0]

        detail_list = []
        for j1 in range(len(data_detail)):
            if (str(data_detail[j1][0]) == str(sbillno)):
                d2 = {}
                d2['fbillno'] = data_detail[j1][0]
                d2['fline'] = data_detail[j1][1]
                d2['frfidbarcode'] = data_detail[j1][2]
                d2['fpackno'] = data_detail[j1][3]
                d2['fpackname'] = data_detail[j1][4]
                detail_list.append(d2)

        d1["details"] = detail_list
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": query.limit,
                 "page": query.pageIndex,
                 "pages": (count + query.limit - 1) // query.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class ContainerScrapDetail(BaseModel):
    """容器报废明细"""
    fline: int = Field(..., description="行号")
    fpackno: str = Field(..., description="包装编号")
    frfidbarcode: str = Field(..., description="RFID条码")


class ContainerScrapCreate(BaseModel):
    """容器报废新增请求"""
    fbillno: str = Field(..., description="单据编号")
    fmrkdate: str = Field(..., description="标记日期(yyyy-MM-ddTHH:mm:ss)")
    fmrkuser: str = Field(..., description="标记人")
    fsiteNo: str = Field(..., description="站点编号")
    fstockno: str = Field("0", description="库存编号，默认'0'")
    fexplanation: str = Field("", description="报废说明，默认空字符串")
    fbilltype: str = Field("1", description="单据类型，默认'1'")
    details: List[ContainerScrapDetail] = Field(..., description="报废明细列表")


@data_mrk_head.post("/create", summary="容器报废新增接口")
def create_mrk_head(container: ContainerScrapCreate, db: Session = Depends(get_db)):
    try:
        fitemcount = 0
        fpiececount = 0
        ftotalamt = 0.0
        fbillno = container.fbillno
        # 返回内容
        d1 = {}
        with db.begin():
            detail_list = []
            jsondata = container.details
            for data in jsondata:
                sql = "insert into i_pack_mrk_detail(fbillno,fline,fpackno,frfidbarcode)"
                sql = sql + " values( :fbillno , :fline , :fpackno , :frfidbarcode )"
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "fline": data.fline,
                    "fpackno": data.fpackno,
                    "frfidbarcode": data.frfidbarcode,
                })
                fitemcount = fitemcount + 1
                fpiececount = fpiececount + 1
                ftotalamt = ftotalamt + 0

                # 返回明细
                d2 = {}
                d2['fbillno'] = fbillno
                d2['fline'] = data.fline
                d2['fpackno'] = data.fpackno
                d2['frfidbarcode'] = data.frfidbarcode
                detail_list.append(d2)

            sql = "insert into i_pack_mrk_head(fbillno,fsite_no,fmrkuser,fmrkdate,fstockno,fitemcount,fpiececount,ftotalamt,fexplanation,fupddate,foperator,fbillstate,fbilltype)"
            sql = sql + " values( :fbillno , :fsiteNo , :fmrkuser , :fmrkdate , :fstockno , :fitemcount , :fpiececount , :ftotalamt , :fexplanation ,now(), :fmrkuser ,1, :fbilltype )"
            db.execute(text(sql), {
                "fbillno": fbillno,
                "fsiteNo": container.fsiteNo,
                "fmrkuser": container.fmrkuser,
                "fmrkdate": container.fmrkdate,
                "fstockno": container.fstockno,
                "fitemcount": fitemcount,
                "fpiececount": fpiececount,
                "ftotalamt": ftotalamt,
                "fexplanation": container.fexplanation,
                "fbilltype": container.fbilltype,
            })
            # 返回
            d1['fbillno'] = fbillno
            d1['fsiteno'] = container.fsiteNo
            d1['fmrkuser'] = container.fmrkuser
            d1['fmrkdate'] = container.fmrkdate
            d1['fstockno'] = container.fstockno
            d1['fitemcount'] = fitemcount
            d1['fpiececount'] = fpiececount
            d1['ftotalamt'] = float(ftotalamt)
            d1['fexplanation'] = container.fexplanation
            d1['fupddate'] = container.fmrkdate
            d1['foperator'] = container.fmrkuser
            d1['fcheckdate'] = None
            d1['fcheckuser'] = None
            d1['fbillstate'] = 1
            d1['fbilltype'] = container.fbilltype
            d1["details"] = detail_list
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@data_mrk_head.post("/update", summary="容器报废修改接口")
def update_mrk_head(container: ContainerScrapCreate, db: Session = Depends(get_db),
                    token: str = Depends(oauth2_scheme)):
    try:
        fitemcount = 0
        fpiececount = 0
        ftotalamt = 0.0
        fbillno = container.fbillno
        userid = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "delete from i_pack_mrk_detail where fbillno= :fbillno "
            db.execute(text(sql), {
                "fbillno": fbillno,
            })
            jsondata = container.details
            for data in jsondata:
                sql = "insert into i_pack_mrk_detail(fbillno,fline,fpackno,frfidbarcode)"
                sql = sql + " values( :fbillno , :fline , :fpackno , :frfidbarcode )"
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "fline": data.fline,
                    "fpackno": data.fpackno,
                    "frfidbarcode": data.frfidbarcode,
                })
                fitemcount = fitemcount + 1
                fpiececount = fpiececount + 1
                ftotalamt = ftotalamt + 0
            sql = "update i_pack_mrk_head "
            sql = sql + " set fstockno= :fstockno ,fbilltype= :fbilltype ,fexplanation= :fexplanation "
            sql = sql + ",fitemcount= :fitemcount ,fpiececount= :fpiececount ,ftotalamt= :ftotalamt "
            sql = sql + ",fupddate=now(),foperator= :userid "
            sql = sql + " where fbillno= :fbillno "
            db.execute(text(sql), {
                "fstockno": container.fstockno,
                "fbilltype": container.fbilltype,
                "fexplanation": container.fexplanation,
                "fitemcount": fitemcount,
                "fpiececount": fpiececount,
                "ftotalamt": ftotalamt,
                "userid": userid,
                "fbillno": fbillno,
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
